package com.admin.util;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.admin.model.ExcelReadResult;
import com.admin.model.FileMeta;
import com.admin.model.ImportExcelReprotModel;
import com.common.model.ClassInfo;
import com.common.model.Project;
import com.common.model.StudentUser;
import com.common.util.Md5Encrypt;
import com.teacher.model.ProjAndProf;

public class AdminStudentExcelTool {
	//至少需要包含的表头
	static private ArrayList<String> excelHeadMinVal=null;
	public AdminStudentExcelTool(){
		if(excelHeadMinVal==null){
			excelHeadMinVal=new ArrayList<>();
			excelHeadMinVal.add("学号");
			excelHeadMinVal.add("姓名");
			excelHeadMinVal.add("密码");
			excelHeadMinVal.add("性别");
			excelHeadMinVal.add("年级");
			excelHeadMinVal.add("班级");
		}
	}
	/**
	 * 读取excel文件
	 * @param excelFile
	 * @return 返回一个包括学生,错误信息等的bean
	 */
	public ExcelReadResult<ImportExcelReprotModel<StudentUser>> readExcel(FileMeta excelFile){
		boolean is2003=excelFile.getFileName().endsWith(".xls");
		if(is2003){
			return readExcelOf2003(excelFile.getInputStresm());
		}else{
			return readExcelOf2007(excelFile.getInputStresm());
		}
	}
	/**
	 * 读取2003版的excel
	 * @param is
	 * @return
	 */
	public ExcelReadResult< ImportExcelReprotModel<StudentUser>> readExcelOf2003(InputStream is){
		HSSFWorkbook wb=null;
		HSSFSheet sheet;
		ExcelReadResult<ImportExcelReprotModel<StudentUser>> result=new ExcelReadResult<>();
		result.setTotalDataCount(0);
		try {
			wb=new HSSFWorkbook(is);
		} catch (Exception e) {
			result.setErrorMsg("文件读取错误");
			return result;
		}
		sheet=wb.getSheetAt(0);
		if(sheet==null){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		HSSFRow excelHead=sheet.getRow(0);
		if(excelHead==null){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		int colCount=excelHead.getLastCellNum();
		if(colCount<excelHeadMinVal.size()){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		HashMap<String, Integer>indexMap=new HashMap<>();
		int check=0;
		for (int i = 0; i < colCount; i++) {
			HSSFCell cell=excelHead.getCell(i);
			if(cell==null){
				continue;
			}
            String cellval=cell.getStringCellValue().toLowerCase().trim();
            if(!indexMap.containsKey(cellval)){
            	indexMap.put(cellval, i);
            	if(excelHeadMinVal.contains(cellval)){
            		check++;
            	}
            }
        }
		if(check<excelHeadMinVal.size()){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		int rowCount=getRowsInSheet(sheet);
		result.setTotalDataCount(rowCount-1);
		if(rowCount==1){
			result.setErrorMsg("没有任何数据");
			return result;
		}
		for(int i=1;i<rowCount;i++){
			HSSFRow row=sheet.getRow(i);
			if(row==null){
				continue;
			}
			ArrayList<String>rowData=new ArrayList<>();
			for(int j=0;j<colCount;j++){
				HSSFCell cell=row.getCell(j);
				if(cell==null){
					rowData.add("");
					continue;
				}
				try {
					rowData.add(cell.getStringCellValue());
				} catch (Exception e) {
					if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
						rowData.add((long)cell.getNumericCellValue()+"");
					}else{
						rowData.add("");
					}
				}
			}
			ImportExcelReprotModel<StudentUser> report=getStudentByRow(rowData, indexMap);
			if(report.getErrorMsg()!=null){
				report.setErrorLine(i+1);
			}
			result.getDataList().add(report);
		}
		return result;
	}
	public ExcelReadResult< ImportExcelReprotModel<StudentUser>> readExcelOf2007(InputStream is){
		ExcelReadResult<ImportExcelReprotModel<StudentUser>> result=new ExcelReadResult<>();
		result.setTotalDataCount(0);
		XSSFWorkbook wb=null;
		XSSFSheet sheet=null;
		try {
			wb=new XSSFWorkbook(is);
		} catch (Exception e) {
			result.setErrorMsg("文件读取错误");
			return result;
		}
		sheet=wb.getSheetAt(0);
		if(sheet==null){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		XSSFRow excelHead=sheet.getRow(0);
		if(excelHead==null){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		int colCount=excelHead.getLastCellNum();
		if(colCount<excelHeadMinVal.size()){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		HashMap<String, Integer>indexMap=new HashMap<>();
		int check=0;
		for (int i = 0; i < colCount; i++) {
			XSSFCell cell=excelHead.getCell(i);
			if(cell==null){
				continue;
			}
            String cellval=cell.getStringCellValue().toLowerCase().trim();
            if(!indexMap.containsKey(cellval)){
            	indexMap.put(cellval, i);
            	if(excelHeadMinVal.contains(cellval)){
            		check++;
            	}
            }
        }
		if(check<excelHeadMinVal.size()){
			result.setErrorMsg("文件格式错误,请参考示例文件");
			return result;
		}
		int rowCount=getRowsInSheet(sheet);
		result.setTotalDataCount(rowCount-1);
		if(rowCount==1){
			result.setErrorMsg("没有任何数据");
			return result;
		}
		for(int i=1;i<rowCount;i++){
			XSSFRow row=sheet.getRow(i);
			if(row==null){
				continue;
			}
			ArrayList<String>rowData=new ArrayList<>();
			for(int j=0;j<colCount;j++){
				XSSFCell cell=row.getCell(j);
				if(cell==null){
					rowData.add("");
					continue;
				}
				try {
					rowData.add(cell.getStringCellValue());
				} catch (Exception e) {
					if(cell.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						rowData.add((long)cell.getNumericCellValue()+"");
					}else{
						rowData.add("");
					}
				}
			}
			ImportExcelReprotModel<StudentUser> report=getStudentByRow(rowData, indexMap);
			if(report.getErrorMsg()!=null){
				report.setErrorLine(i+1);
			}
			result.getDataList().add(report);
		}
		return result;
	}
	/**
	 * 根据行数据和航索引生成包含学生及错误信息的bean
	 * @param row
	 * @param map
	 * @return
	 */
	public ImportExcelReprotModel<StudentUser> getStudentByRow(ArrayList<String> row,HashMap<String, Integer> map){
		ImportExcelReprotModel<StudentUser> report=new ImportExcelReprotModel<>();
		StudentUser student=new StudentUser();
		ClassInfo classinfo=new ClassInfo();
		student.setClassInfo(classinfo);
		report.setData(student);
		String no=row.get(map.get("学号"));
		if(no==null||no.trim().isEmpty()){
			report.setErrorMsg("未设置学号");
			return report;
		}
		report.getData().setNo(no.trim());
		String name=row.get(map.get("姓名"));
		if(name==null||name.trim().isEmpty()){
			report.setErrorMsg("未设置姓名");
			return report;
		}
		report.getData().setName(name.trim());
		String gender=row.get(map.get("性别"));
		if(name==null||name.trim().isEmpty()){
			report.setErrorMsg("未设置性别");
			return report;
		}
		if(gender.indexOf("男")!=-1){
			gender="男";
		}else{
			gender="女";
		}
		report.getData().setGender(gender);
		String grade=row.get(map.get("年级"));
		if(grade==null||grade.trim().isEmpty()){
			report.setErrorMsg("未设置年级");
			return report;
		}
		report.getData().setGrade(grade.trim());
		String password=row.get(map.get("密码"));
		if(password==null||password.trim().isEmpty()){
			report.setErrorMsg("未设置密码");
			return report;
		}
		report.getData().setPassword(Md5Encrypt.encrypt(password.trim()));
		String classname=row.get(map.get("班级"));
		if(classname==null||classname.trim().isEmpty()){
			report.setErrorMsg("未设置班级");
			return report;
		}
		report.getData().getClassInfo().setName(classname.trim());
		Integer qqIndex=map.get("qq");
		if(qqIndex!=null){
			String qq=row.get(qqIndex);
			if(qq!=null){
				report.getData().setQq(qq.trim());
			}
		}
		Integer telIndex=map.get("电话");
		if(telIndex!=null){
			String tel=row.get(telIndex);
			if(tel!=null){
				report.getData().setTelephone(tel.trim());
			}
		}
		return report;
	}
	/**
	 * 2003版获取实际excel的行数
	 * @param sheet
	 * @return
	 */
	public int getRowsInSheet(HSSFSheet sheet){
		int maxRow=sheet.getLastRowNum();
		int actureRowCount=0;
		for(int i=0;i<maxRow+1;i++){
			HSSFRow row=sheet.getRow(i);
			int maxCol=row.getLastCellNum();
			boolean colHasData=false;
			for(int j=0;j<maxCol;j++){
				HSSFCell cell=row.getCell(j);
				if(cell==null){
					continue;
				}
				try {
					String val=cell.getStringCellValue();
					if(val==null||val.trim().isEmpty()){
						continue;
					}
				} catch (Exception e) {
				}
				colHasData=true;
				break;
			}
			if(colHasData){
				actureRowCount=i+1;
			}
		}
		return actureRowCount;
	}
	public int getRowsInSheet(XSSFSheet sheet){
		int maxRow=sheet.getLastRowNum();
		int actureRowCount=0;
		for(int i=0;i<maxRow+1;i++){
			XSSFRow row=sheet.getRow(i);
			int maxCol=row.getLastCellNum();
			boolean colHasData=false;
			for(int j=0;j<maxCol;j++){
				XSSFCell cell=row.getCell(j);
				if(cell==null){
					continue;
				}
				try {
					String val=cell.getStringCellValue();
					if(val==null||val.trim().isEmpty()){
						continue;
					}
				} catch (Exception e) {
				}
				colHasData=true;
				break;
			}
			if(colHasData){
				actureRowCount=i+1;
			}
		}
		return actureRowCount;
	}
	public static XSSFWorkbook exportTo2007(ArrayList<StudentUser> stuList) {
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet();
		XSSFRow headRow = sheet.createRow(0);
		headRow.createCell(0).setCellValue("学号");
		headRow.createCell(1).setCellValue("姓名");
		headRow.createCell(2).setCellValue("班级");
		headRow.createCell(3).setCellValue("系");
		headRow.createCell(4).setCellValue("学院");
		int stuListSize = stuList.size();
		for (int i = 0; i < stuListSize; i++) {
			StudentUser stu = stuList.get(i);
			XSSFRow row = sheet.createRow(i + 1);
			row.createCell(0).setCellValue(stu.getNo());
			row.createCell(1).setCellValue(stu.getName());
			row.createCell(2).setCellValue(stu.getClassInfo().getName());
			row.createCell(3).setCellValue(stu.getClassInfo().getBranch().getProf().getDepartment().getName());
			row.createCell(4).setCellValue(stu.getClassInfo().getBranch().getProf().getDepartment().getCollege().getName());
		}
		return wb;
	}

	public static HSSFWorkbook exportTo2003(ArrayList<StudentUser> stuList) {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFRow headRow = sheet.createRow(0);
		headRow.createCell(0).setCellValue("学号");
		headRow.createCell(1).setCellValue("姓名");
		headRow.createCell(2).setCellValue("班级");
		headRow.createCell(3).setCellValue("系");
		headRow.createCell(4).setCellValue("学院");
		int stuListSize = stuList.size();
		for (int i = 0; i < stuListSize; i++) {
			StudentUser stu = stuList.get(i);
			HSSFRow row = sheet.createRow(i + 1);
			row.createCell(0).setCellValue(stu.getNo());
			row.createCell(1).setCellValue(stu.getName());
			row.createCell(2).setCellValue(stu.getClassInfo().getName());
			row.createCell(3).setCellValue(stu.getClassInfo().getBranch().getProf().getDepartment().getName());
			row.createCell(4).setCellValue(stu.getClassInfo().getBranch().getProf().getDepartment().getCollege().getName());
		}
		return wb;
	}
}
